package com.example.demo.service.database;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.mapper.DataBaseConfigMapper;
import com.example.demo.pojo.DataBaseConfig;
import com.example.demo.pojo.vo.DataBaseAdd;
import com.example.demo.pojo.vo.QuerySql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;


import javax.sql.DataSource;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Service
public class DatabaseServiceImpl  extends ServiceImpl<DataBaseConfigMapper, DataBaseConfig>  implements  IDatabaseService{
    @Autowired
    private DataSource dataSource;

    @Override
    public String  createDatabase(DataBaseAdd dataBaseAdd) throws Exception {
        LocalDateTime start = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String startTime = start.format(formatter);
        try (Connection connection = dataSource.getConnection()) {
            Statement statement = connection.createStatement();
            System.out.println("开始创建数据库: " + dataBaseAdd.getDatabaseName() + " at " + startTime);
            // 使用传入的字符集编码创建数据库
            String createDbSql = "CREATE DATABASE " + dataBaseAdd.getDatabaseName() + " CHARACTER SET " +
                    dataBaseAdd.getCharacterEncoding() + " COLLATE " + dataBaseAdd.getCharacterEncoding() + "_general_ci";
            //查询数据库是否存在
            String checkDbSql = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" + dataBaseAdd.getDatabaseName() + "'";
            ResultSet resultSet = statement.executeQuery(checkDbSql);
            if (resultSet.next()) {
                throw new Exception("数据库已存在！");
            }else {
                statement.execute(createDbSql);
            }
            System.out.println("数据库 " + dataBaseAdd.getDatabaseName() + " 创建成功！");
            statement.close();
        }
        LocalDateTime end = LocalDateTime.now();
        String endTime = end.format(formatter);
        //保存进数据库
        DataBaseConfig dataBaseConfig = new DataBaseConfig();
        dataBaseConfig.setDatabaseName(dataBaseAdd.getDatabaseName());
        dataBaseConfig.setCreateTime(endTime);
        this.save(dataBaseConfig);
        System.out.println("数据库创建结束: " + endTime);
        return "success";
    }

    @Override
    public List<DataBaseConfig> getAllDatabases() throws Exception {
//        List<String> databases = new ArrayList<>();
//        try (Connection connection = dataSource.getConnection()) {
//            Statement statement = connection.createStatement();
//            ResultSet resultSet = statement.executeQuery("SHOW DATABASES");
//            while (resultSet.next()) {
//                databases.add(resultSet.getString(1));
//            }
//            resultSet.close();
//            statement.close();
//        }
//        return databases;
        List<DataBaseConfig> dataBaseConfigs = this.list();
        return dataBaseConfigs;
    }

    @Override
    public void querySql(QuerySql querySql) throws SQLException {
        //传进来的数据库名
        String databaseName = querySql.getDatabaseName();
        // 获取数据库连接
        try (Connection connection = dataSource.getConnection()) {
            // 设置连接的catalog为传入的数据库名
            connection.setCatalog(databaseName);
            // 检查数据库是否存在
            boolean databaseExists = connection.getCatalog() != null && connection.getCatalog().equalsIgnoreCase(databaseName);
            if (!databaseExists) {
                throw new Exception("数据库不存在！");
            }
            // 如果数据库存在，执行SQL语句
            try (Statement statement = connection.createStatement()) {
                statement.execute(querySql.getSql());
            } catch (SQLException e) {
                throw new Exception("执行SQL失败：" + e.getMessage());
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<String> getAllTables(String databaseName) throws SQLException {
        List<String> tables = new ArrayList<>();
        try (Connection connection = dataSource.getConnection()) {
            // 获取数据库元数据
            DatabaseMetaData metaData = connection.getMetaData();
            // 查询指定数据库的所有表
            ResultSet resultSet = metaData.getTables(databaseName, null, "%", new String[]{"TABLE"});
            while (resultSet.next()) {
                // 获取表名
                String tableName = resultSet.getString("TABLE_NAME");
                tables.add(tableName);
            }
            resultSet.close();
        } catch (SQLException e) {
            throw new SQLException("查询数据库表失败：" + e.getMessage());
        }
        return tables;
    }

}
